excel question

"mr_gofast" (jake_berenshteyn)
08/12/2014 at 13:17 • Filed to: HELP, EXCEL

Hey guys

so im wondering, if i have ten records listed like this

2.2

2.45

3.4

3.67

5.2

5.3

5.7

6.1

each number represents a defect on a road somewhere..i need a formula that will count up how many miles have defects on them. Defective miles being any mile that has a defect on it, whether its got 5 defects or just 1.

ideally the answer i get in excel will be that there are 6 defective miles in that chart above ; mile 1, mile 2 (2 records but still one mile) mile 3 ( 2 records still one defective mile), mile 4, mile 5 (with 4 records) and mile 6.

how do i do it, can i do it??

DISCUSSION (19)

HammerheadFistpunch > mr_gofast
08/12/2014 at 13:21

I don't think I understand your dataset.

CalzoneGolem > mr_gofast
08/12/2014 at 13:21

I would convert them all to no decimal places then sort ti in the next column I would put =if(a1=a2, "dupe" , "-") then I would copy that column and paste back values and delete any row with dupe in it. Then it would be a simple matter of seeing how many rows it takes.

CalzoneGolem > HammerheadFistpunch
08/12/2014 at 13:24

He wants to know how many different first digits there are.

1, 2, 3, 4, 5 and 6 in this case so the answer would be 6.

mr_gofast > HammerheadFistpunch
08/12/2014 at 13:25

i have a big excel file full of recoreds, the records are a list of defects on a rail track in a certain region. My boss wants the number of defective miles in the region, in order to do that he has indicated that for every mile of track with a defect on it, is classified as defective. Those numbers up there are the defects, however if a mile has more then one defect its still defective - so if mile 1 has one defect i count it as one defective mile, if mile 2 has5 defects, it also counts as one defective mile, i need to figure out a formula to add up how many 'defective' miles each region has. I just cant get past the fact that there will be double counting.

Gamecat235 > mr_gofast
08/12/2014 at 13:32

Use a =rounddown for the first column, then do a =countif against those values with a table of values for all whole integers.

Gamecat235 > Gamecat235
08/12/2014 at 13:36

!!! UNKNOWN CONTENT TYPE !!!

Gamecat235 > mr_gofast
08/12/2014 at 13:41

I added a table to my comment. To sort of show what I meant.

Mattbob > mr_gofast
08/12/2014 at 13:42

I would make a loop that iterates through integers and compares the values in the list to the current and last integers you are iterating through. If the value in the list falls between say 2 and 3 ( value >= n and < n+1), have it increment a value in position 2 of an array. That array can then tell you how many defects in each mile. To just determine if there is an array in a particular mile, you can take, say "mile 7", see if the value in position 7 is greater than 7, and bam you know if it is defective.

I'm sure there are better ways to do this... Just an idea.

Mattbob > Gamecat235
08/12/2014 at 13:45

I always forget what you can do with just excel without resorting to VBA.

Gamecat235 > Mattbob
08/12/2014 at 13:48

Right. I use VBA for EVERYTHING. but sometimes formulas work quicker.

mr_gofast > Gamecat235
08/12/2014 at 13:50

however what i need from the rounddown values is that there are 6 defective miles, 1,2,3,4,5 and 6.

Gamecat235 > mr_gofast
08/12/2014 at 13:57

OK, so use what I modified here for the check value. It's a nested if statement. =IF(ISNA(MATCH(C2,B$2:B$12,0)),0,1) this returns a "1" if the value is matched in the table.

Alternately, you could have it show the number in question with a

=IF(ISNA(MATCH(C2,B$2:B$12,0)),0,C2) type nested statement

!!! UNKNOWN CONTENT TYPE !!!

cazzyodo > mr_gofast
08/12/2014 at 14:10

Did you figure it out?

mr_gofast > cazzyodo
08/12/2014 at 14:12

im counting the miles manually..ifailz

cazzyodo > mr_gofast
08/12/2014 at 14:13

I'll give it a whip.

cazzyodo > cazzyodo
08/12/2014 at 14:31

If you have a column rounding down the mile marker (like I saw you posted) and then a little spot where you list out the mile and whether it has a defect, try this:

Columns are A - D

!!! UNKNOWN CONTENT TYPE !!!

where the formula for that fourth column is =IF(COUNTIF(B:B,C3),"Defect","None")

(stupid no picture in comment thing made this difficult)

cazzyodo > mr_gofast
08/12/2014 at 14:31

replied to myself first...whoops

If you have a column rounding down the mile marker (like I saw you posted) and then a little spot where you list out the mile and whether it has a defect, try this:

Columns are A - D

!!! UNKNOWN CONTENT TYPE !!!

where the formula for that fourth column is =IF(COUNTIF(B:B,C3),"Defect","None")

(stupid no picture in comment thing made this difficult)

mr_gofast > cazzyodo
08/12/2014 at 14:43

thanks for the help - i think i got it.

theloudmouth > mr_gofast
08/12/2014 at 21:36

stackoverflow.com